package com.skyhigh.web.webservice.persistence;

import java.io.Serializable;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.jdbc.Work;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StandardBasicTypes;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.StoredProcedure;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.HibernateObjectRetrievalFailureException;
import org.springframework.orm.hibernate3.HibernateTemplate;

import com.skyhigh.web.webservice.domain.Usuario;
import com.skyhigh.web.webservice.exceptions.ExceptionHelper;
import com.skyhigh.web.webservice.exceptions.QueryException;
import com.skyhigh.web.webservice.exceptions.RecordNotFoundException;

public class DefaultDAOImpl implements GenericDAO{
	
	private HibernateTemplate hbTemplate;
	
	public void setSessionFactory(SessionFactory sessionFactory) {
		hbTemplate = new HibernateTemplate(sessionFactory);
	}	

	public int count(Class entity) {
		List list = hbTemplate.find(
		    "select count(*) from " + entity.getName() + " x");
		Long count = (Long) list.get(0);
		return count.intValue();
	}
	
	public <T> T get(Class<T> entity, Serializable id) {			
		return (T) hbTemplate.get(entity, id);
	}
	
	@SuppressWarnings("unchecked")
	public <T> List<T> getAll(Class<T> entity) {
		return hbTemplate.find("from " + entity.getName() + " x");
	}
	
	@SuppressWarnings("unchecked")
	public <T> T load(Class<T> entity, Serializable id) {
		return (T) hbTemplate.load(entity, id);
	}
	
	public Object save(Object entity){
		return hbTemplate.save(entity);
	}
		
	public void update(Object entity){
		hbTemplate.update(entity);
	}
	
	public void delete(Object entity) {
		hbTemplate.delete(entity);
	}
	
	public void deleteById(Class entity, Serializable id) throws RecordNotFoundException{
		try{
			Object obj = load(entity,id);
			hbTemplate.delete(obj);
		}catch(HibernateObjectRetrievalFailureException ex){
			throw new RecordNotFoundException(ex);
		}
	}

	public void deleteAll(final Class entity) {
		hbTemplate.execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
			    String hqlDelete = "delete " + entity.getName();
			    int deletedEntities = session.createQuery(hqlDelete).executeUpdate();
			    return null;
			}		
		});
	}
	
	public void deleteAll(List entities){
		hbTemplate.deleteAll(entities);
	}
	
	public boolean exists(Object entity) {
		Class cls = entity.getClass();
		Method m = null;	
		Object id = null;
		try{
			m=cls.getMethod("getId",null);
			if(m != null)
				id=m.invoke(entity,null);
		} catch (IllegalArgumentException ex) {
			ExceptionHelper.logEx(ex);
		} catch (IllegalAccessException ex) {
			ExceptionHelper.logEx(ex);
		} catch (InvocationTargetException ex) {
			ExceptionHelper.logEx(ex);			
		} catch (NoSuchMethodException ex) {
			ExceptionHelper.logEx(ex);
		}
		
    	try{    		
    		if(id != null){    			
    			List list = hbTemplate.find(
    				    "select count(*) from " + cls.getSimpleName() + " where id = " + id);    			
    			Long count = (Long) list.get(0);
    			return count > 0;
    		}else{    		
	    		List result = hbTemplate.findByExample(entity) ;
	            return result != null && result.size() > 0;
    		}
    	}catch(RuntimeException dex){
    		throw dex;
    	} 
	}
	
	public <T> List<T> find(String query) {
    	try{
            return hbTemplate.find(query);
    	}catch(RuntimeException dex){
    		throw dex;
    	} 
	}

	public <T> List<T> find(String query, Object id) {
    	try{
            return hbTemplate.find(query, id);
    	}catch(RuntimeException dex){
    		throw dex;
    	} 
	}

	public <T> List<T> find(String query, Object[] param) {
    	try{
            return hbTemplate.find(query, param);
    	}catch(RuntimeException dex){
    		throw dex;
    	} 
	}
	
	public <T> T findFirst(String query) {
		List<T> result = hbTemplate.find(query);
		if(result != null && result.size() > 0){
			return result.get(0);
		}
		return null;
	}
	
	public List execute(Query query) throws QueryException{
		 //Verifies id is a SQL query
		 if(query.getNature().equals(QueryConstants.JPA_QUERY)){
			 return performORMQuery(query);
		 }else if(query.getNature().equals(QueryConstants.HIBERNATE_NAMED_QUERY)){
			 return performNamedQuery(query);			 
		 }else if(query.getNature().equals(QueryConstants.SQL_QUERY)){
			 return performSQLQuery(query);
		 }else if(query.getNature().equals(QueryConstants.PREPARED_QUERY)){
			 return performORMQuery(query);			 
		 }
		 throw new QueryException("Query nature not supported.");
	 }

	private List performORMQuery(Query query){
		String statement = query.getStatement();
		List<Parameter> parameters = query.getParameters();
		Object[] queryInput = new Object[parameters.size()];
		for(Parameter param : parameters){
			queryInput[param.getIndex()] = param.getValue();
		}
		return find(statement, queryInput);
	}
	
	private List performNamedQuery(Query query) {
    	try{
    		String statement = query.getStatement();
    		List parameters = query.getParameters();    		
	        Session session = hbTemplate.getSessionFactory().openSession();
	        org.hibernate.Query q = session.getNamedQuery(statement);
	        if (parameters != null) {
	            Iterator iter = parameters.iterator();
	            while (iter.hasNext()) {
	                Parameter p = (Parameter) iter.next();
	                q.setParameter(p.getIndex(), p.getValue(), p.getType());
	            }
	        }
	        List ret = q.list();
	        session.close();
	        return ret;
    	}catch(RuntimeException dex){
    		throw dex;
    	}	        
    }

	
	private List performSQLQuery(Query query){
	   	try{
    		String beanMapping = query.getMappingClassName();
    		Class beanMappingClass = query.getMappingClass(); 
    		String statement = query.getStatement();
    		List parameters = query.getParameters();
    		
	    	Session session=hbTemplate.getSessionFactory().openSession();
	    	org.hibernate.Query hbQuery = null;
	        if(beanMapping == null && beanMappingClass == null){
	        	hbQuery=session.createSQLQuery(statement);        	
	        }else{
	        	Class beanResult = null;
	        	if(beanMapping != null){
					try {
						beanResult = Class.forName(beanMapping);
					} catch (ClassNotFoundException e) {
						e.printStackTrace();
					}
	        	}else{
	        		beanResult = beanMappingClass;
	        	}
	        	hbQuery=session.createSQLQuery(statement).setResultTransformer(Transformers.aliasToBean(beanResult));        	
	        }
	        if(parameters != null){
				Iterator iter = parameters.iterator();
				while(iter.hasNext()){
					Parameter param = (Parameter) iter.next();
					//verifies is contains a null value
					if(param.getValue() != null){
						hbQuery.setParameter(param.getIndex(), param.getValue(), param.getType());
					}else{
						hbQuery.setParameter(param.getIndex(), null, StandardBasicTypes.STRING);
					}
				}        	
	        }
	        List response=hbQuery.list();
	        session.close();
	        return response;
		}catch(RuntimeException dex){	    		
			throw dex;
		}	        
	}	
	
	private List performPreparedQuery(Query query) {
		PreparedQueryWork queryWork = new PreparedQueryWork(query); 
		Session session=hbTemplate.getSessionFactory().openSession();
		session.doWork(queryWork);
		return queryWork.getData();
	}	

	public int executeUpdate(Query query){
		try{
			String statement = query.getStatement();
			List<Parameter> parameters = query.getParameters();			
			Session session=this.hbTemplate.getSessionFactory().openSession();
			org.hibernate.Query q = session.createSQLQuery(statement);
			if(parameters != null){
				Iterator iter = parameters.iterator();
				while(iter.hasNext()){
					Parameter p= (Parameter) iter.next();
					q.setParameter(p.getIndex(),p.getValue(),p.getType());
				}        	
			}
			int result=q.executeUpdate();
			session.close();
			return result;
    	}catch(RuntimeException dex){
    		throw dex;
    	}			
	}
	
	public int executeDelete(Query query){
		return executeUpdate(query);
	}	
	
	public Map executeProcedure(Query query) throws QueryException{
		BasicProcedure sp = new BasicProcedure(null, query);
		return sp.execute();
	}
	
	
	public boolean iniciarSesion(Usuario entity, String password) {
		Class cls = entity.getClass();		
    	try{    	
    		boolean exists = exists(entity); 
    		if(exists){    
    			/*
    			List list = hbTemplate.find(
    				    "select count(*) from " + cls.getSimpleName() + " where id = " + id);    			
    			Long count = (Long) list.get(0);
    			return count > 0;
    			*/
    			QuerySQL query = new QuerySQL("select * from " + cls.getSimpleName() + " where idUsuario = " + entity.getIdUsuario());
    			query.setMappingClass(entity.getClass());
    			List list;
				try {
					list = this.execute(query);
					list.get(0);
					Usuario usuario = (Usuario) list.get(0);
	    			if(usuario.getPassword().equals(password))
	    				return true;
	    			else
	    				return false;
				} catch (QueryException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return false;
				}    				        			    		    	
    		}else{    		
    			return false;
    		}
    	}catch(RuntimeException dex){
    		throw dex;
    	} 
	}
}

class PreparedQueryWork implements Work{
	
	private List data = new ArrayList();
	private Query prepatedQuery;
	
	PreparedQueryWork(Query query){
		this.prepatedQuery = query;
	}
	
	@Override
	public void execute(Connection con) throws SQLException {
		try {
			String statement = prepatedQuery.getStatement();
			List<Parameter> parameters = prepatedQuery.getParameters();			
			Statement st = con.createStatement();
			PreparedStatement ps = con.prepareStatement(statement);
			if(parameters != null){
				Iterator iter = parameters.iterator();
				while(iter.hasNext()){
					Parameter p= (Parameter) iter.next();
					int index=p.getIndex()+1;
					String typeName=TypeHelper.getTypeName(p);
					if(typeName.equals("INTEGER")){
						ps.setInt(index,((Integer)p.getValue()).intValue());
					}else if(typeName.equals("LONG")){
						ps.setLong(index,((Long)p.getValue()).longValue());
					}else if(typeName.equals("SHORT")){
						ps.setShort(index,((Short)p.getValue()).shortValue());
					}else if(typeName.equals("BYTE")){
						ps.setByte(index,((Byte)p.getValue()).byteValue());
					}else if(typeName.equals("FLOAT")){
						ps.setFloat(index,((Float)p.getValue()).floatValue());
					}else if(typeName.equals("DOUBLE")){
						ps.setDouble(index,((Double)p.getValue()).doubleValue());
					}else if(typeName.equals("CHARACTER")){
						ps.setString(index,p.getValue().toString());
					}else if(typeName.equals("TIMESTAMP")){
						ps.setTimestamp(index,(Timestamp)p.getValue());
					}else{
						ps.setObject(index,p.getValue());	
					}									
				}        	
			}

			ResultSet rs=ps.executeQuery();
			ResultSetMetaData metaData = rs.getMetaData();
			int rowCount = metaData.getColumnCount();
			while(rs.next()){
				Map row = new LinkedHashMap();
				for (int i=0; i < rowCount; i++) {
					String columnName = metaData.getColumnName(i + 1);
					row.put(columnName,rs.getObject(columnName));
				}
				data.add(row);
			}
			
			rs.close();
			ps.close();
		} catch (Exception e) {
			System.out.println(e);
		}
	}

	public List getData() {
		return data;
	}

	public void setData(List data) {
		this.data = data;
	}
	
}

class BasicProcedure extends StoredProcedure {
    
	private Query query;
	
    public BasicProcedure(DataSource ds, Query query) {
    	this.query = query;
		String statement = query.getStatement();
		List<Parameter> parameters = query.getParameters();	
		
        setDataSource(ds);
        setSql(statement);
        
        if(parameters != null){
			Iterator iter = parameters.iterator();
			while(iter.hasNext()){
				Parameter p= (Parameter) iter.next();
				
				declareParameter(new SqlParameter(p.getName().toString(), TypeHelper.getTypeName(p).equals("INTEGER") ? java.sql.Types.INTEGER : java.sql.Types.VARCHAR));
				declareParameter(new SqlParameter(p.getName().toString(), TypeHelper.getTypeName(p).equals("STRING") ? java.sql.Types.VARCHAR : java.sql.Types.VARCHAR));
				declareParameter(new SqlParameter(p.getName().toString(), TypeHelper.getTypeName(p).equals("DOUBLE") ? java.sql.Types.DOUBLE : java.sql.Types.VARCHAR));
				declareParameter(new SqlParameter(p.getName().toString(), TypeHelper.getTypeName(p).equals("TIMESTAMP") ? java.sql.Types.TIMESTAMP : java.sql.Types.VARCHAR));
			}        	
		}        
        
        compile();
    }

    public Map execute() {
    	Map inputs = new HashMap();
        
        List<Parameter> parameters = query.getParameters();	
        if(parameters != null){
			Iterator iter = parameters.iterator();
			while(iter.hasNext()){
				Parameter p= (Parameter) iter.next();
				
				 inputs.put(p.getName(), p.getValue());  
				
			}        	
		}           
        
        return execute(inputs);
    }       
}
